package cn.com.dashihui.web.service;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Locale;
import java.util.Map;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

import cn.com.dashihui.web.common.OrderCode;

public class OrderReportService {
	
	/*自营商品订单统计*/
	
	/**
	 * 查询自营商品的订单统计数据
	 */
	public Map<String,Object> queryOrderStatesCount(){
		StringBuffer sqlBuffer = new StringBuffer("SELECT ");
		List<Object> sqlParams = new ArrayList<Object>();
		//1.自营商品总量
		sqlBuffer.append("(SELECT COUNT(*) FROM v_goods WHERE isSelf=1) goodsCount,");
		
		//2.统计“待付款”的商品订单数量（在线支付、未付款、正常）
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_order WHERE isSelf=1 AND payType=? AND payState=? AND orderState=?) state1,");
		sqlParams.add(OrderCode.OrderPayType.ON_LINE);
		sqlParams.add(OrderCode.OrderPayState.NO_PAY);
		sqlParams.add(OrderCode.OrderState.NORMAL);
		
		//3.统计查询“待发货”的订单数量（1：“在线支付+送货上门”、“已支付”、“未发货”、“正常”，2：“货到付款+送货上门”、“未发货”、“正常”）
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_order WHERE isSelf=1 AND takeType=?");
		sqlBuffer.append(" AND (");
		sqlBuffer.append("(payType=? AND payState=?)");
		sqlBuffer.append(" OR payType=?");
		sqlBuffer.append(") AND deliverState=? AND orderState=?) state2,");
		sqlParams.add(OrderCode.OrderTakeType.DELIVER);
		sqlParams.add(OrderCode.OrderPayType.ON_LINE);sqlParams.add(OrderCode.OrderPayState.HAD_PAY);
		sqlParams.add(OrderCode.OrderPayType.ON_DELIVERY);
		sqlParams.add(OrderCode.OrderDeliverState.NO_DELIVER);sqlParams.add(OrderCode.OrderState.NORMAL);
		
		//4.统计查询“待签收”的订单数量（“送货上门”、“已发货”、“正常”）
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_order WHERE isSelf=1");
		sqlBuffer.append(" AND takeType=? AND deliverState=?");
		sqlBuffer.append(" AND orderState=?) state3,");
		sqlParams.add(OrderCode.OrderTakeType.DELIVER);sqlParams.add(OrderCode.OrderDeliverState.HAD_DELIVER);
		sqlParams.add(OrderCode.OrderState.NORMAL);
		
		//5.统计查询“被催单”的订单数量（“正常”、“催单次数!=0”）
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_order WHERE isSelf=1");
		sqlBuffer.append(" AND orderState=? AND urgeTimes!=0) state4");
		sqlParams.add(OrderCode.OrderState.NORMAL);

		Record result = Db.findFirst(sqlBuffer.toString(),sqlParams.toArray());
		return result.getColumns();
	}
	
	/**
	 * 查询自营商品的订单统计图表数据
	 */
	public List<Record> queryOrderOfSevenDay(){
		String sql = "SELECT"
				+ " DATE_FORMAT(DD.dateDate,'%m月%d日') date,IFNULL(B.total,0) total"
				+ " FROM t_dict_date DD"
				+ " LEFT JOIN (SELECT DATE_FORMAT(A.createDate,'%Y-%m-%d') DOFY,COUNT(*) total FROM t_bus_order A WHERE A.isSelf=1 AND A.createDate BETWEEN ? AND ? GROUP BY DOFY) B ON DD.dateDate=B.DOFY"
				+ " WHERE DD.dateDate BETWEEN ? AND ?"
				+ " ORDER BY DD.dateDate ASC";
		String timeFrom = getDateTimeBefore(9), timeTo = getNowDateTime();
		return Db.find(sql,timeFrom,timeTo,timeFrom,timeTo);
	}
	
	/**
	 * 查询指定店铺的交易成功订单统计图表数据
	 * @param storeid
	 */
	public List<Record> queryFinishOrderOfSevenDay(){
		String sql = "SELECT"
				+ " DATE_FORMAT(DD.dateDate,'%m月%d日') date,IFNULL(B.total,0) total"
				+ " FROM t_dict_date DD"
				+ " LEFT JOIN (SELECT DATE_FORMAT(A.createDate,'%Y-%m-%d') DOFY,COUNT(*) total FROM t_bus_order A WHERE A.isSelf=1 AND A.createDate BETWEEN ? AND ? AND orderState=? GROUP BY DOFY) B ON DD.dateDate=B.DOFY"
				+ " WHERE DD.dateDate BETWEEN ? AND ?"
				+ " ORDER BY DD.dateDate ASC";
		return Db.find(sql,getDateTimeBefore(10),getNowDateTime(),OrderCode.OrderState.FINISH,getDateTimeBefore(10),getNowDateTime());
	}
	
	/*工具方法*/
	private SimpleDateFormat ymd = new SimpleDateFormat("yyyy-MM-dd");
	private SimpleDateFormat ymdhms = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	//获取当前日期时间
	private String getNowDateTime(){
		return ymdhms.format(new Date());
	}
	//获取N天前的日期时间
	private String getDateTimeBefore(int amount){
		Calendar calc = Calendar.getInstance(Locale.CHINESE);
		calc.add(Calendar.DATE, -amount);
		return ymd.format(calc.getTime())+" 00:00:00";
	}
}
